﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace PagesList
{
    public partial class RiTaiZhangPage : UserControl
    {
        public RiTaiZhangPage()
        {
            InitializeComponent();
           // ShowData();
        }

        public void ShowData()
        {
            OleDbCommandBuilder oledbCmdBuilder;
            ds = new DataSet();
            string sql = null;
            connection = ConnectManager.GetSystemConnect();
            if (textBox1.Text == ""&&textBox2.Text==""&&textBox3.Text=="")
            {
                sql = "select  * from ksd_CWrishouru where 送检日期>=CONVERT(varchar(23),'" + dateTimePicker1.Value + "',23) and 送检日期<=CONVERT(varchar(23),'" + dateTimePicker2.Value + "',23)";
            }
            else if (textBox2.Text == "" && textBox3.Text == "")
            {
                sql = "select * from ksd_CWrishouru where 送检医院 like '%" + textBox1.Text + "%' and 送检日期>=CONVERT(varchar(23),'" + dateTimePicker1.Value + "',23) and 送检日期<=CONVERT(varchar(23),'" + dateTimePicker2.Value + "',23)";
            }
            else if (textBox1.Text == "" && textBox3.Text == "")
            {
                sql = "select * from ksd_CWrishouru where 省区 ='" + textBox2.Text + "' and 送检日期>=CONVERT(varchar(23),'" + dateTimePicker1.Value + "',23) and 送检日期<=CONVERT(varchar(23),'" + dateTimePicker2.Value + "',23)";
            }

            else if (textBox1.Text == "" && textBox2.Text == "")
            {
                sql = "select * from ksd_CWrishouru where 市 ='" + textBox3.Text + "' and 送检日期>=CONVERT(varchar(23),'" + dateTimePicker1.Value + "',23) and 送检日期<=CONVERT(varchar(23),'" + dateTimePicker2.Value + "',23)";
            }
            else if (textBox1.Text =="")
            {
                sql = "select * from ksd_CWrishouru where 市 ='" + textBox3.Text + "' and 省区 ='" + textBox2.Text + "' and 送检日期>=CONVERT(varchar(23),'" + dateTimePicker1.Value + "',23) and 送检日期<=CONVERT(varchar(23),'" + dateTimePicker2.Value + "',23)";
            }

            try
            {
                oledbAdapter = new OleDbDataAdapter(sql, connection);
                oledbCmdBuilder = new OleDbCommandBuilder(oledbAdapter);
                oledbAdapter.Fill(ds);
                this.dataGridView1.DataSource = ds.Tables[0];
       
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        protected void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
        {
            if (ds == null || oledbAdapter == null)
            {
                return;
            }
            oledbAdapter.Update(ds);
        }

        private OleDbConnection connection;
        private OleDbDataAdapter oledbAdapter;
        private DataSet ds;

        private void RiTaiZhangPage_Resize(object sender, EventArgs e)
        {
            this.dataGridView1.Size = this.Size - new System.Drawing.Size(100, 100);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ShowData();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ExcelExportTool.DataGridViewToExcel(this.dataGridView1);
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            OleDbCommandBuilder oledbCmdBuilder;
            ds = new DataSet();
            string sql = null;
            connection = ConnectManager.GetSystemConnect();
            sql = "select * from ksd_CWrishouru where 项目编码 in (select jymddh from xt_jymd where jymdlb='MAYO')"
                 + " and 送检日期>=CONVERT(varchar(23),'" + dateTimePicker1.Value + "',23) and 送检日期<=CONVERT(varchar(23),'" + dateTimePicker2.Value + "',23)";
            try
            {
                oledbAdapter = new OleDbDataAdapter(sql, connection);
                oledbCmdBuilder = new OleDbCommandBuilder(oledbAdapter);
                oledbAdapter.Fill(ds);
                this.dataGridView1.DataSource = ds.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }


    }
}
